import os
import pandas as pd
from sqlalchemy import create_engine


def _gen_engine():
  engine = create_engine(
      'mysql+mysqldb://feed_stat_querier:#dXfc%DGtXg%SfBX@coin-db.corp.prestolabs.io/coin_feed_stat_20180514',
      echo=False)
  return engine


def read_csv_into_df(csv_root, machine, trading_date, exchange, symbol):
  td_str = trading_date.strftime('%Y%m%d')
  filename = "%s.csv" % symbol
  csv_path = os.path.join(csv_root, machine, td_str, exchange, filename)
  df = pd.read_csv(csv_path, sep=',', header=0)
  df['datetime'] = pd.to_datetime(df['timestamp'], unit='ns')
  df = df[['datetime', 'true_bid', 'true_ask', 'bid0', 'ask0']]
  df = df.set_index('datetime')
  return df


def calculate_spread_bp(df):
  true_book_spread = df['true_ask'] - df['true_bid']
  mid_price = (df['bid0'] + df['ask0']) * 0.5
  percentage = true_book_spread / mid_price
  percentage_bp = percentage.mean() * 10000
  return percentage_bp


def calculate_true_book_spread(df, interval_sec):
  true_book_spread = df['true_ask'] - df['true_bid']
  interval = '%dS' % interval_sec
  true_book_spread_sum = true_book_spread.resample(interval).sum()
  true_book_spread_cnt = true_book_spread.resample(interval).count()
  avg_true_book_spread = true_book_spread_sum / true_book_spread_cnt
  return avg_true_book_spread


def calculate_volatility(df, interval_sec, unit_sec=10):
  mid_price = (df['ask0'] + df['bid0']) / 2
  unit = '%dS' % unit_sec
  interval = '%dS' % interval_sec
  first_mid_price_in_unit = mid_price.resample(unit).first()
  last_mid_price_in_unit = mid_price.resample(unit).last()
  rtn = (last_mid_price_in_unit / first_mid_price_in_unit) - 1
  volatility = rtn.resample(interval).std()
  return volatility


def query_total_turnover(exchange, start_date, end_date):
  sql = """
SELECT symbol, exchange, sum(total_volume * vwap) as turnover 
FROM StatsView
WHERE git_commit_datetime="2018-07-11 11:24:12"
AND exchange = %s
AND trading_date >= "20180701" AND trading_date <= "20180710"
AND symbol LIKE %s
GROUP BY exchange, symbol ORDER BY turnover DESC LIMIT 10;
"""
  engine = _gen_engine()
  turnover = pd.read_sql(sql, engine, params=(
      exchange,
      "%USDT",
  ))
  return turnover
